--- title: "sp72268_get_cancel_status stored procedure documentation" summary: "Retrieved Oracle function that derives collection-oriented cancellation status labels for one policy." status: draft owner: "SAIF Claims BOF team" last_reviewed: "2026-08-25" source_schema: "WCIS_DBA" source_name: "sp72268_get_cancel_status" source_type: "FUNCTION" retrieval_status: "found" retrieval_command: "legends oracle query --name sp72268_get_cancel_status" retrieval_date: "2026-08-25" --- # sp72268_get_cancel_status ## Overview | Field | Value | | --- | --- | | **Schema** | `WCIS_DBA` | | **Type** | Standalone function | | **Database** | Oracle | | **Calling libraries** | `SAIF.Business.View.Policy` | | **Calling classes** | `clsPolicyData` | ## Purpose `sp72268_get_cancel_status` derives a human-readable cancellation or collection status for a policy by combining policy status-group data with collection, audit, payroll-report, balance, and submission-ownership checks. ## Procedures / Methods ### sp72268_get_cancel_status | Field | Value | | --- | --- | | **Returns** | `VARCHAR2` | | **Called by** | `clsPolicyData` ad hoc retrieval and `pk73094_policy_data.getpolicy` | | **Description** | Returns the derived cancellation-status label for one policy number. | #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_policy_num` | IN | `NUMBER` | — | Policy number to evaluate. | ## Business Rules The retrieved database source embeds the following server-side rules. | Rule ID | Summary | | --- | --- | | [BR-VP-029](#br-vp-029-pending-cancel-policies-only-derive-the-demanded-status) | Policies in status group `PENDCA` run only the demand-status branch. | | [BR-VP-030](#br-vp-030-cancelled-policies-use-a-fixed-precedence-order-for-derived-collection-statuses) | Cancelled policies evaluate Judgment, Lien, Defaulted, Demanded, Pending Audit, Pending PR, Closed w/Bal, then Closed. | | [BR-VP-031](#br-vp-031-defaulted-and-demanded-statuses-come-from-policy-demand-response-codes) | `Defaulted` requires response code `D`; `Demanded` requires a policy-demand row with a null response code. | | [BR-VP-032](#br-vp-032-application-policies-return-saifquote-only-when-wcis-does-not-own-the-submission) | Status group `APP` yields `saifQuote` only when `pk73102_policy.does_wcis_own_submission(...) = 'N'`. | ### BR-VP-029 Pending-cancel policies only derive the demanded status | Field | Value | | --- | --- | | **Statement** | When the policy status group is `PENDCA`, the function derives cancellation status only by checking for a demand row with `COLLECTION_ACT_RESPONSE_CODE IS NULL`. | | **Trigger** | `SP72268_GET_CANCEL_STATUS` executes for a policy whose `policy_status_group_code` is `PENDCA`. | | **Enforcement** | The main program routes `PENDCA` to `pending_cancel_status`, which calls only `look_for_demand_status`. | | **Outcome** | A pending-cancel policy returns `Demanded` when an open demand exists; otherwise it returns null. | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json` | --- ### BR-VP-030 Cancelled policies use a fixed precedence order for derived collection statuses | Field | Value | | --- | --- | | **Statement** | For cancelled policies, the function derives collection status in a fixed order: Judgment, Lien, Defaulted, Demanded, Pending Audit, Pending PR, Closed w/Bal, and finally Closed. | | **Trigger** | `SP72268_GET_CANCEL_STATUS` executes for a policy whose `policy_status_group_code` is `CANCEL`. | | **Enforcement** | `cancel_status` calls the local derivation procedures only while `v_cancel_status` remains null, then defaults to `Closed`. | | **Outcome** | The first matching collection condition determines the returned cancellation status. | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json` | --- ### BR-VP-031 Defaulted and demanded statuses come from policy-demand response codes | Field | Value | | --- | --- | | **Statement** | The function derives `Defaulted` from a `POLICY_DEMAND` row whose `COLLECTION_ACT_RESPONSE_CODE = 'D'` and derives `Demanded` from a `POLICY_DEMAND` row whose response code is null. | | **Trigger** | `look_for_default_status` or `look_for_demand_status` executes. | | **Enforcement** | Each local procedure opens a cursor over `POLICY_DEMAND` with its respective response-code filter and assigns the corresponding status when a row is found. | | **Outcome** | Demand and default statuses are driven directly by the collection-action response state. | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json` | --- ### BR-VP-032 Application policies return saifQuote only when WCIS does not own the submission | Field | Value | | --- | --- | | **Statement** | For application-status policies, the function returns `saifQuote` only when `pk73102_policy.does_wcis_own_submission(in_policy_num) = 'N'` and no prior status has been assigned. | | **Trigger** | `SP72268_GET_CANCEL_STATUS` executes for a policy whose `policy_status_group_code` is `APP`. | | **Enforcement** | The main program routes `APP` to `saifquote_status`, which tests `pk73102_policy.does_wcis_own_submission` before assigning `v_cancel_status := 'saifQuote'`. | | **Outcome** | Application policies are labeled `saifQuote` only for non-WCIS-owned submissions. | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json` | ## Operations The retrieved database source implements the following internal operations. | Operation | Description | Trigger | | --- | --- | --- | | [Derive pending-cancel demand status](#derive-pending-cancel-demand-status) | Check open demand rows and return `Demanded` for pending-cancel policies. | Policy status group is `PENDCA`. | | [Derive cancelled-policy collection status](#derive-cancelled-policy-collection-status) | Evaluate collection, audit, payroll-report, and balance conditions in precedence order for cancelled policies. | Policy status group is `CANCEL`. | | [Derive application saifQuote status](#derive-application-saifquote-status) | Return `saifQuote` for application policies not owned by WCIS. | Policy status group is `APP`. | ### Derive pending-cancel demand status | Field | Value | | --- | --- | | **Description** | Check open demand rows and return `Demanded` for pending-cancel policies. | | **Trigger** | Policy status group is `PENDCA`. | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json` | ### Derive cancelled-policy collection status | Field | Value | | --- | --- | | **Description** | Evaluate collection, audit, payroll-report, and balance conditions in precedence order for cancelled policies. | | **Trigger** | Policy status group is `CANCEL`. | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json` | ### Derive application saifQuote status | Field | Value | | --- | --- | | **Description** | Return `saifQuote` for application policies not owned by WCIS. | | **Trigger** | Policy status group is `APP`. | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json` | ## Calling code references | Class | Method | Source File | | --- | --- | --- | | `clsPolicyData` | `ad hoc method` | `SAIF.Business.View.Policy\clsPolicyData.vb` | ## Source Code
PL/SQL function source ```sql FUNCTION SP72268_GET_CANCEL_STATUS /**************************************************************** *Procedure Title : Get cancel status. *Name : SP72268_GET_CANCEL_STATUS *Called by : forms ***************************************************************** *PROGRAMMER ANALYST DATE MOD # *CARADE CARADE 01/17/2001 Combo 5.4.1; csr 8 *Description: This stored procedure will derive the cancellation status * for a policy. * * INPUTS: policy number * RETURNS: cancel status - varchar2(13) * * Maintenance Log: * Release Name Date Description * 7.0.3 genhyd 06/15/2006 - Changed to use the policy_demand table instead of policy_demand_action. * 7.0.3 valrey 05/22/2006 - Enhanced the collection status. * 7.0.2 bricot 02/11/2005 - remove references to payroll summary * and audit schedule tables * Retro Cancel 5.4.1 Carade 02/26/01 Added derivation of demand and * default statuses. * 7.0.3 CSR86 leedas 08/24/2006 - Corrected the spelling Judgment used in look_for_judgement. * OSQ leedas 02/07/2008 - Changed to also derive saifQuote if the wcis system did not own the * application ***********************************************************************************************/ ( in_policy_num IN NUMBER) RETURN VARCHAR2 IS /******************* Local Variables *************************/ v_cancel_status VARCHAR2(13); v_count NUMBER; CURSOR pol_cur IS SELECT pol.POLICY_STATUS_CODE, pol.current_balance, psc.policy_status_group_code FROM POLICY pol, POLICY_STATUS_CODE psc WHERE pol.policy_num = in_policy_num AND pol.POLICY_STATUS_CODE = psc.code; pol_rec pol_cur%ROWTYPE; /********************************************************************/ /*********************** Local Procedures *************************/ /********************************************************************/ PROCEDURE look_for_default_status /* derive the default status */ IS CURSOR default_csr IS SELECT policy_num FROM POLICY_DEMAND WHERE policy_num = in_policy_num AND COLLECTION_ACT_RESPONSE_CODE = 'D'; default_rec default_csr%ROWTYPE; BEGIN IF default_csr%isopen THEN CLOSE default_csr; END IF; OPEN default_csr; FETCH default_csr INTO default_rec; IF default_csr%FOUND THEN v_cancel_status := 'Defaulted'; END IF; CLOSE default_csr; END; /* look_for_default_status */ PROCEDURE look_for_demand_status /* derive the demand status */ IS CURSOR demand_csr IS SELECT policy_num FROM POLICY_DEMAND WHERE policy_num = in_policy_num AND COLLECTION_ACT_RESPONSE_CODE IS NULL; demand_rec demand_csr%ROWTYPE; BEGIN IF demand_csr%isopen THEN CLOSE demand_csr; END IF; OPEN demand_csr; FETCH demand_csr INTO demand_rec; IF demand_csr%FOUND THEN v_cancel_status := 'Demanded'; END IF; CLOSE demand_csr; END; /* look_for_demand_status */ PROCEDURE look_for_pending_audit_status /* derive the pending audit status */ IS /* Look for assigned or received audit with an effective date prior to the cancel date. If the cancel date is the same as the audit start date then there was no coverage and we don't care about any pending audits. Only physical audits are considered. */ CURSOR au_cur IS SELECT policy_num FROM POLICY_PERIOD_PAYROLL_AUDIT pppa, AUDIT_CODE ac WHERE pppa.policy_num = in_policy_num AND pppa.AUDIT_STATUS_CODE IN ('A', 'R') AND pppa.audit_period_start_date < (SELECT cancel_date FROM CANCELLATION can WHERE policy_num = in_policy_num AND cancellation_seq_num = (SELECT MAX(cancellation_seq_num) FROM CANCELLATION can2 WHERE policy_num = in_policy_num)) AND pppa.AUDIT_CODE = ac.code AND ac.AUDIT_TYPE_CODE IN ('PHYS', 'PHYSR'); au_rec au_cur%ROWTYPE; BEGIN IF au_cur%isopen THEN CLOSE au_cur; END IF; OPEN au_cur; FETCH au_cur INTO au_rec; IF au_cur%FOUND THEN v_cancel_status := 'Pending Audit'; END IF; CLOSE au_cur; END; /* look_for_pending_audit_status */ PROCEDURE look_for_pending_pr_status /* derive the pending payroll report status */ IS /* Look for initial or pending payroll reports for reporting periods with a start date prior to the cancel date. If the cancel date is the same as the period start date then there was no coverage and we don't care about any pending payroll reports as of the cancel date. The payroll reports selected are for other than physical audits. */ CURSOR pr_cur IS SELECT policy_num FROM POLICY_PERIOD_PAYROLL_AUDIT pppa WHERE policy_num = in_policy_num AND AUDIT_STATUS_CODE IN ('A','I','R') AND audit_period_start_date < (SELECT cancel_date FROM CANCELLATION can WHERE policy_num = in_policy_num AND cancellation_seq_num = (SELECT MAX(cancellation_seq_num) FROM CANCELLATION can2 WHERE policy_num = in_policy_num)) AND NOT EXISTS (SELECT 1 FROM POLICY_PERIOD_PAYROLL_AUDIT pppa1, AUDIT_CODE ac WHERE pppa1.payroll_seq_num = pppa.payroll_seq_num AND pppa1.AUDIT_CODE = ac.code AND ac.AUDIT_TYPE_CODE IN ('PHYS', 'PHYSR')); pr_rec pr_cur%ROWTYPE; BEGIN IF pr_cur%isopen THEN CLOSE pr_cur; END IF; OPEN pr_cur; FETCH pr_cur INTO pr_rec; IF pr_cur%FOUND THEN v_cancel_status := 'Pending PR'; END IF; CLOSE pr_cur; END; /* look_for_pending_pr_status */ PROCEDURE look_for_closed_bal_status /* derive the pending payroll report status */ IS BEGIN /* The policy balance was retrieved with the policy cursor. */ IF NVL(pol_rec.current_balance,0) = 0 THEN NULL; ELSE v_cancel_status := 'Closed w/Bal'; END IF; END; /* look_for_closed_bal_status */ PROCEDURE look_for_judgement IS BEGIN SELECT COUNT(*) INTO v_count FROM COLLECTION_LEGAL_ACTION cla WHERE cla.POLICY_NUM = in_policy_num AND cla.COLLECTION_ACTION_CODE = '018' AND cla.CANCEL_DATE IS NULL; IF v_count > 0 THEN v_cancel_status := 'Judgment'; END IF; END; PROCEDURE look_for_lien IS BEGIN SELECT COUNT(*) INTO v_count FROM COLLECTION_LEGAL_ACTION cla WHERE cla.POLICY_NUM = in_policy_num AND cla.COLLECTION_ACTION_CODE IN ('016','017') AND cla.CANCEL_DATE IS NULL; IF v_count > 0 THEN v_cancel_status := 'Lien'; END IF; END; PROCEDURE pending_cancel_status /* derive the cancel status for a pending cancel policy */ IS BEGIN look_for_demand_status; END; /* pending_cancel_status */ PROCEDURE cancel_status /* derive the cancel status for a cancelled policy */ IS BEGIN look_for_judgement; IF v_cancel_status IS NULL THEN look_for_lien; END IF; IF v_cancel_status IS NULL THEN look_for_default_status; END IF; IF v_cancel_status IS NULL THEN look_for_demand_status; END IF; IF v_cancel_status IS NULL THEN look_for_pending_audit_status; END IF; IF v_cancel_status IS NULL THEN look_for_pending_pr_status; END IF; IF v_cancel_status IS NULL THEN look_for_closed_bal_status; END IF; /* If no other status was derived then set it closed */ IF v_cancel_status IS NULL THEN v_cancel_status := 'Closed'; END IF; END; /* cancel_status */ PROCEDURE saifquote_status /* derives saifQuote status for an application */ IS BEGIN IF pk73102_policy.does_wcis_own_submission(in_policy_num) = 'N' AND v_cancel_status IS NULL THEN v_cancel_status := 'saifQuote'; END IF; END; /* saifquote_status */ /********************************************************************/ /*********************** Main Program *************************/ /********************************************************************/ BEGIN IF pol_cur%isopen THEN CLOSE pol_cur; END IF; OPEN pol_cur; FETCH pol_cur INTO pol_rec; CLOSE pol_cur; IF pol_rec.policy_status_group_code = 'PENDCA' THEN pending_cancel_status; ELSIF pol_rec.policy_status_group_code = 'CANCEL' THEN cancel_status; ELSIF pol_rec.policy_status_group_code = 'APP' THEN saifquote_status; END IF; RETURN v_cancel_status; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20002,'Error in sp72268: ' || SQLERRM); END; /*SP72268_GET_CANCEL_STATUS */ ```
## Related documentation - [Procedure catalog](index.md) - [Business rules](../business-rules.md) - [Operations and processes](../operations-processes.md) - [SQL catalog](../sql/index.md) ## Sources - Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json` - Calling code: `SAIF.Business.View.Policy\clsPolicyData.vb` - Calling package: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk73094_policy_data.json`